
import glob, re, string
n = '\x0D\x0A'

class lines:
    ''' Read big text file as iterator. '''
    def __init__(self , flpath_s):
        self.flpath_s = flpath_s
        self.sfile_fl = open(flpath_s, 'rb')

    def __getitem__( self , index ):
        line_s = self.sfile_fl.readline()
        if line_s:
            return line_s
        else:
            raise IndexError

def positions(text, indexes, char=','):
    ''' Return list of positions of given character in text. '''
    for i, c in enumerate(text):
        if c <> char:
            indexes[i] = '-'
    return indexes

pf_masks = [r"""EFLADM_20081110_WREUZP_EN61_2008.CSV.added-end-space"""]

# http://msdn.microsoft.com/en-us/library/ms191234.aspx
xml = ["""<?xml version="1.0"?>
<BCPFORMAT  
xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">""" + n + \
"""<RECORD>""" + n,
#    repeat xml_charfixed ...
#    xml_charterm
""" </RECORD><ROW> """ + n,
#    repeat xml_column
""" </ROW></BCPFORMAT> """ + n]

xml_charfixed_template = '''  <FIELD ID="column_number" xsi:type="CharFixed" LENGTH="fixed_span"/>'''
xml_charterm_template =  '''  <FIELD ID="column_number" xsi:type="CharTerm" TERMINATOR="\\r\\n"/>'''
xml_column_template = '''  <COLUMN SOURCE="column_number" NAME="column_name" xsi:type="SQLNVARCHAR" LENGTH="255"/>'''

ll = {}
for pfm in pf_masks:
    for f in glob.glob(pfm):
        #print f
        fname = string.split(f, '\x5C')[-1]
        if fname not in ('xxx'):
    
            #fo = open(fname + '.fmt.xml', 'wb')
            fo = open('fmt.xml', 'wb')
            fo.write(xml[0])
            
            lino = 0; len_line = 0
            for line in lines(f):
                lino += 1 #; print lino
                len_line = len(line); 
                if ll.has_key('ll_'+str(len_line)): ll['ll_'+str(len_line)] += 1
                else: ll['ll_'+str(len_line)] = 1
                line = re.sub('\x0D', '', line)
                line = re.sub('\x0A', '', line)
        
                if lino == 1:
                    header_columns = string.split(line, ',')
                elif lino == 2:
                    indexes = range(0, len(line))
                elif lino > 100:
                    break
                else:
                    indexes = positions(line, indexes)
        
            sorted_indexes = sorted(indexes)
            si = sorted_indexes[:sorted_indexes.index('-')] # remove '-'
            si.append(len_line)
            #print si, n header_columns

            idx = 0
            for i, c in enumerate(header_columns):
                idx = i # print i,
                if idx <> 0: fo.write('''  <FIELD ID="%s" xsi:type="CharFixed" LENGTH="1"/>''' % (i+1000,) + n)
                xml_charfixed = re.sub('column_number', str(i+1), xml_charfixed_template)
                if i == 0: #first column
                    xml_charfixed = re.sub('fixed_span', str(si[i]), xml_charfixed)
                elif i == len(header_columns) - 1: # last column
                    xml_charfixed = re.sub('fixed_span', str(si[i] - si[i-1] - 3), xml_charfixed)
                else: # any other column
                    xml_charfixed = re.sub('fixed_span', str(si[i] - si[i-1] - 1), xml_charfixed)
                fo.write(xml_charfixed + n)
        
            xml_charterm = re.sub('column_number', str(idx+2), xml_charterm_template)
            fo.write(xml_charterm + n)
        
            fo.write(xml[1])
            
            for i, c in enumerate(header_columns):
                xml_column = re.sub('column_number', str(i+1), xml_column_template)
                #xml_column = re.sub('column_name', re.sub('[^a-zA-Z0-9_]', '_', c).upper(), xml_column)
                xml_column = re.sub('column_name', c.upper(), xml_column)
                fo.write(xml_column + n)
            
            fo.write(xml[2])
            fo.close()
            
